<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="com.uc.training.smadmin.gds.dao.CommentDao">

    <!--获取评论列表-->
    <select id="getCommentList" parameterClass="com.uc.training.smadmin.gds.vo.CommentListVO"
            resultClass="com.uc.training.smadmin.gds.re.CommentRE">
        select
            c.id,
            c.goods_id  AS goodsId,
            c.goods_property_id AS goodsPropertyId,
            CONCAT(LEFT(c.member_nickname,1), '**' ,RIGHT(c.member_nickname,1)) AS memberNickname,
            c.member_image_url AS memberImageUrl,
            c.order_goods_id as orderGoodsId,
            c.parent_id AS parentId,
            c.goods_property_name AS goodsPropertyName,
            c.`status` AS status,
            c.goods_sroce AS goodsSroce,
            c.content AS content,
            c.create_time AS createTime,
            c1.id  AS  appendId,
            c1.`status` AS appendStatus,
            c1.content  AS  appendContent,
            c1.create_time  AS  appendTime
        from t_gds_comment c
        LEFT JOIN t_gds_comment c1
        ON
        c.id = c1.parent_id
        WHERE  c.parent_id is null
        AND
        <![CDATA[
          #lowSroce#  <= c.goods_sroce
          ]]>
        and
        <![CDATA[
        c.goods_sroce <= #highSroce#
         ]]>
        <isNotEmpty property="status" prepend="and">
            c.`status` = #status#
        </isNotEmpty>
        <isNotEmpty property="id" prepend="and">
            c.id = #id#
        </isNotEmpty>
        <isNotEmpty property="goodsId" prepend="and">
            c.goods_id = #goodsId#
        </isNotEmpty>
        <isNotEmpty property="goodsPropertyId" prepend="and">
            c.goods_property_id = #goodsPropertyId#
        </isNotEmpty>
        ORDER BY c.create_time DESC
        <isNotNull property="offset">
            LIMIT #offset#, #pageSize#
        </isNotNull>
    </select>

    <select id="getCountBySroce" parameterClass="com.uc.training.smadmin.gds.vo.CommentListVO" resultClass="Integer">
       SELECT COUNT(1)
       FROM t_gds_comment c
         where
         c.parent_id is null
         AND
         c.goods_id = #goodsId#
         and
          <![CDATA[
          #lowSroce#  <= c.goods_sroce
          ]]>
        and
        <![CDATA[
        c.goods_sroce <=  #highSroce#
         ]]>
    </select>

    <!--插入评论返回ID-->
    <insert id="addComment"  parameterClass="com.uc.training.smadmin.gds.vo.CommentVO">
        INSERT INTO t_gds_comment
        (
        goods_id,
        member_id,
        member_nickname,
        goods_property_id,
        goods_property_name,
        goods_sroce,
        status,
        parent_id,
        content,
        order_goods_id,
        create_time
        )
        VALUES (
        #goodsId#,
        #memberId#,
        #memberNickname#,
        #goodsPropertyId#,
        #goodsPropertyName#,
        #goodsSroce#,
        #status#,
        #parentId#,
        #content#,
        #orderGoodsId#,
        now()
        )
        <selectKey resultClass="long" keyProperty="id">
            SELECT LAST_INSERT_ID() as id;
        </selectKey>
    </insert>

    <insert id="addCommentPics" parameterClass="com.uc.training.smadmin.gds.vo.CommentPicVO">
        <![CDATA[
        insert into t_gds_comment_pic (
        comment_id,
        image_url,
        create_time
        )
        values
        ]]>
        <iterate conjunction ="," property="picUrl" >
            <![CDATA[
            (
            #commentId#,
            #picUrl[]#,
            now()
            )
            ]]>
        </iterate>
    </insert>

    <!--获取评论总数和评论平均分-->
    <select id="getCommentCountAndAvg" resultClass="com.uc.training.smadmin.gds.vo.CommentAvgVO"  parameterClass="com.uc.training.smadmin.gds.vo.CommentListVO">
      SELECT
      COUNT(1) as count ,
      AVG(goods_sroce) as sroceAvg
      FROM t_gds_comment
      where
        parent_id is null
        AND
        <![CDATA[
          #lowSroce#  <= goods_sroce
          ]]>
        and
        <![CDATA[
          goods_sroce <= #highSroce#
         ]]>
        <isNotEmpty property="status" prepend="and">
            status = #status#
        </isNotEmpty>
        <isNotEmpty property="goodsId" prepend="and">
         goods_id = #goodsId#
        </isNotEmpty>
    </select>

   <!--根据ID修改评论状态-->
    <update id="editCommentById" parameterClass="com.uc.training.smadmin.gds.vo.CommentVO">
        UPDATE t_gds_comment
        set status = #status#
        WHERE id = #id#
    </update>

    <delete id="delCommentById">
        DELETE FROM t_gds_comment
        where id = #commentId#
    </delete>

    <select id="getCommentCountByOrdergoodsId" resultClass="com.uc.training.smadmin.gds.model.Comment">
        SELECT
        s.id as id,
        s.goods_id as goodsId,
        s.member_id as memberId,
        s.member_nickname as memberNickname,
        s.goods_property_id as goodsPropertyId,
        s.goods_property_name as goodsPropertyName,
        s.goods_sroce as goodsSroce,
        s.status as status,
        s.parent_id as parentId,
        s.order_goods_id as orderGoodsId
        FROM t_gds_comment s
        WHERE s.order_goods_id = #orderGoodsId#
        ORDER by s.create_time DESC
    </select>

</sqlMap>